Loading TOC...

POST /manage/v2/databases/{id|name}/view-schemas/{schema-name}/views

Summary

This resource address adds a SQL View to the specified schema on the specified database.

URL Parameters
format The format of the posted data. Can be either html, json, or xml (default). This value overrides the Accept header if both are present.
Request Headers
Accept The expected MIME type of the request body. If the format? parameter is present, it takes precedence over the Accept header.
Content-type The MIME type of the data in the request body. Depending upon the value of the format parameter or Accept header, one of application/xml, application/json, or text/html.
Response Headers
Content-type The MIME type of the data in the response body. Depending upon the value of the format parameter or Accept header, one of application/xml, application/json, or text/html.
Location If the request causes a restart, a Location header is included in the reponse. The header contains a path with which to construct a URL to usable to test when the restart has completed.

Response

Upon success, MarkLogic Server returns status code 201 (Created). If the schema already exists or if the payload is malformed, a status code of 400 (Bad Request) is returned. A status code of 401 (Unauthorized) is returned if the user does not have the necessary privileges.

Required Privileges

This operation requires the manage-admin role, or the following privilege:

http://marklogic.com/xdmp/privileges/manage-admin

Usage Notes

To add a SQL View, named "myTable1," the structure of the data in the request body is as follows.

The view-name property is required and will be converted to all lowercase. The schema-name property must be selected from the list of view-schemas. By default, view-schema-name is set to main. The scope property can be either none (default), {"collection":""}, or {"namespace-uri":"", "localname":""}. The columns properties must reference existing indexes on the database.

Note: The properties described here are for XML payloads. In general they are the same for JSON, with the exception that, in JSON, columns, views, and permissions are expressed in singular form. For example, in JSON, permissions is instead permission and the format is: "permission":[{"role-name":"name", "capability":"cap"}].

The following is the payload expected to create a column for each type of range index.

Path Range Index: 


     "path-reference":{
         "path-expression":"path", 
         "scalar-type":"type", 
         "collation":"http://marklogic.com/collation/codepoint"
     }

Element Range Index (The collation element is optional): 


     "element-reference":{
        "namespace-uri":"", 
        "localname":"name", 
        "scalar-type":"type", 
        "collation":"http://marklogic.com/collation/"
     }

Attribute Range Index (The collation element is optional): 


     "element-attribute-reference":{
        "parent-namespace-uri":"", 
        "parent-localname":"name", 
        "namespace-uri":"", 
        "localname":"name", 
        "scalar-type":"type", 
        "collation":"http://marklogic.com/collation/"
     }

Field Index: 


     "field": [{
        "field-name": "name",
     }]

For details on schemas and views, see SQL on MarkLogic Server in the SQL Data Modeling Guide.

view-name

The name of a relational view. This will be the SQL view name.

schema-name

The name of a relational schema. This will be the SQL schema name.

element-scope

A view scope defined by a root element.

This is a complex structure with the following children:

namespace-uri

The namespace URI of an element.

localname

A local name of an element.

collection-scope

A view scope defined by a collection.

This is a complex structure with the following children:

collection

ordered

Whether the columns in the view must appear in order in the document.

columns

The columns in the view.

This is a complex structure with the following children:

column

A column in a relational view defined over some range index.

This is a complex structure with the following children:

column-name

The name of the column in the relational view. This will be the SQL column name.

cts:base-reference

fields

This is a complex structure with the following children:

field

This is a complex structure with the following children:

field-name

permissions

The default set of permissions used in document creation.

This is a complex structure with the following children:

permission

Permission representation.

This is a complex structure with the following children:

role-name

The Role name (unique)

capability

The action/task permitted by a permission

Example


cat myTable1.json
==> 

{
     "view-name": "myTable1",
     "element-scope":{"namespace-uri":"", "localname":"message"},
     "column": [
       {
         "column-name": "message_list",
         "element-attribute-reference": {
            "parent-namespace-uri" : "",
            "parent-localname": "message",
            "namespace-uri":"",
            "localname": "list",
            "scalar-type":"string"
         }
       },
       {
         "column-name": "subject",
         "element-reference": {
            "namespace-uri": "",
            "localname": "subject",
            "scalar-type": "string" 
         }
       },
       {
         "column-name": "body_url",
         "path-reference": {
            "path-expression": "/message/body/url",
            "scalar-type": "anyURI",
            "collation":"http://marklogic.com/collation/codepoint"
         }
     }],
     "field": [
         {"field-name": "position"}
      ]
}
 
curl -X POST  --anyauth --user $MANAGEADMIN:$MANAGEPASS \
--header "Content-Type:application/json" -d @myTable1.json \
http://localhost:8002/manage/v2/databases/SQLschemas/view-schemas/main/views?format=json

==> Creates a SQL view, named "myTable1", in the 'main' schema
    in the SQLschemas database.

Stack Overflow iconStack Overflow: Get the most useful answers to questions from the MarkLogic community, or ask your own question.